using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using TaugeLibrary.Classes;
using System.Collections;

namespace TaugeFramework.Classes
{
        class Database 
        {
            #region memvar
            private SqlConnectionData m_sqlconnectiondata;
            private string m_dbid = "";
            private string m_desc = "";
            private string m_appid = "";

            private string m_backup_path = @"C:\";
            #endregion

            #region properties
            public SqlConnectionData ConnectionData
            {
                set { m_sqlconnectiondata = value; }
                get { return m_sqlconnectiondata; }
            }

            public string DbId { set { m_dbid = value; } get { return m_dbid; } }
            public string Description { set { m_desc = value; } get { return m_desc; } }
            public string AppID { set { m_appid = value; } get { return m_appid; } }
            public string BackupPath { set { m_backup_path = value; } get { return m_backup_path; } }

            public string DBServer { set { ConnectionData.DBServer = value; } get { return ConnectionData.DBServer; } }
            public string DBName { set { ConnectionData.DBName = value; } get { return ConnectionData.DBName; } }
            public string DBUser { set { ConnectionData.DBUser = value; } get { return ConnectionData.DBUser; } }
            /*public string DBPassword { set { ConnectionData.DBPassword = value; } get { return ConnectionData.DBPassword; } }*/
            public string DBPasswordHashed { set { ConnectionData.DBPasswordHashed  = value; } get { return ConnectionData.DBPasswordHashed; } }
            public bool DBAuth { set { ConnectionData.DBAuth = value; } get { return ConnectionData.DBAuth; } }
            #endregion

            #region constructors
            public Database()
                : base()
            {
                m_sqlconnectiondata = new SqlConnectionData();
            }

            //deprecated
            /*
            public Database(SqlConnectionData p_sqlconnectiondata)
            {
                m_sqlconnectiondata = p_sqlconnectiondata;
            }
            */

            public Database(string p_dbid, string p_desc, string p_appid, string p_backuppath, SqlConnectionData p_sqlconnectiondata)
            {
                m_dbid = p_dbid;
                m_desc = p_desc;
                m_appid = p_appid;
                m_backup_path = p_backuppath;
                m_sqlconnectiondata = p_sqlconnectiondata;
            }

            #endregion


            #region methods
            public SqlConnection createConnection()
            {
                SqlConnection result = new SqlConnection(m_sqlconnectiondata.getConnectionString());
                result.Open();
                return result;
            }

            public object executeScalar(string p_sql)
            {
                using (DataTable dt = this.executeDataTable(p_sql))
                {
                    if (dt.Rows.Count > 0)
                    {
                        return dt.Rows[0][0];
                    }
                    else
                    {
                        return null;
                    }
                }

            }

            public object executeScalar(string p_sql, object p_default)
            {
                object x = executeScalar(p_sql);
                if (x == null)
                {
                    return p_default;
                }
                else
                {
                    return x;
                }

            }

            public SqlDataReader executeReader(string p_sql)
            {
                return executeReader(new SqlCommand(p_sql));
            }

            public SqlDataReader executeReader(SqlCommand p_sc)
            {
                SqlConnection cn = createConnection();
                p_sc.Connection = cn;
                SqlDataReader sdr = p_sc.ExecuteReader(CommandBehavior.CloseConnection);
                p_sc.Connection = null;
                return sdr;
            }

            public int executeNonQuery(string p_sql)
            {
                return executeNonQuery(new SqlCommand(p_sql));
            }

            public int[] executeNonQuery(List<SqlCommand> p_sc, SqlTransaction p_st)
            {
                return executeNonQuery(p_sc.ToArray(), p_st);
            }

            public int[] executeNonQuery(string[] p_sql, SqlTransaction p_st)
            {
                SqlCommand[] sc = new SqlCommand[p_sql.Length];
                int i = 0;

                foreach (string s in p_sql)
                {
                    sc[i] = new SqlCommand(s);
                    i++;
                }
                return executeNonQuery(sc, p_st);
            }

            public int executeNonQuery(SqlCommand p_sc)
            {
                int result;
                using (SqlConnection cn = createConnection())
                {
                    p_sc.Connection = cn;
                    result = p_sc.ExecuteNonQuery();
                    cn.Close();
                    return result;
                }
            }

            public int[] executeNonQuery(SqlCommand[] p_sc, SqlTransaction p_st, params string[] p_output)
            {
                int[] result = new int[p_sc.Length];

                using (SqlConnection cn = createConnection())
                {
                    object outvalue = null;
                    string outparam = "";
                    p_st = cn.BeginTransaction();
                    int i = 0;
                    foreach (SqlCommand sc in p_sc)
                    {
                        sc.Connection = cn;
                        sc.Transaction = p_st;
                        if (i == 0)
                        {
                            if (p_output.Length > 0)
                            {
                                outparam = p_output[0];
                                outvalue = sc.Parameters[outparam].Value;
                            }

                        }
                        else
                        {
                            if (outparam != "")
                            {
                                if (p_sc[i].Parameters.Count > 0)
                                {
                                    p_sc[i].Parameters[outparam].Value = outvalue;
                                }
                                else
                                {
                                    p_sc[i].CommandText = p_sc[i].CommandText.Replace(outparam, LibConstants.toSql(outvalue));
                                }
                            }
                        }
                        try
                        {
                            result[i] = sc.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            p_st.Rollback();
                            cn.Close();
                            throw ex;
                        }
                        sc.Connection = null;
                        sc.Transaction = null;
                        i++;
                    }
                    p_st.Commit();
                    cn.Close();
                }
                return result;
            }

            public DataTable executeDataTable(string p_strsql)
            {
                return executeDataTable(new SqlCommand(p_strsql));
            }

            public DataTable executeDataTable(SqlCommand p_sc)
            {
                using (SqlConnection cn = createConnection())
                {
                    p_sc.Connection = cn;
                    SqlDataAdapter da = new SqlDataAdapter(p_sc);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    cn.Close();
                    p_sc.Connection = null;

                    //PENDING Program.fMain.addMessage(p_sc.CommandText);

                    return ds.Tables[0];
                }
            }


            public string newTempTable()
            {
                string cnamatable = "";
                int x = 0;
                Random rnd = new Random(DateTime.Now.Millisecond);
                try
                {
                    x = rnd.Next(1000000);
                    cnamatable = "TempTable" + x.ToString().Trim();
                    executeScalar("SELECT * FROM " + cnamatable);
                    return newTempTable();
                }
                catch
                {
                    return cnamatable;
                }
            }


            public string Backup()
            {
                string tmpfile = m_backup_path + @"\" + m_sqlconnectiondata.DBName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".BAK";
                this.executeNonQuery(" BACKUP DATABASE " + m_sqlconnectiondata.DBName + " TO DISK = " + LibConstants.toSql(tmpfile) + " WITH INIT");
                return tmpfile;
            }


            /*
        public void Restore(string ps_filesource , string ps_database, string ps_logical_filename , string ps_physical_filename, string ps_logical_logname , string ps_physical_logname , bool pb_replace )
            Dim ls_OldDbName As String = ms_database
            mo_conn.ChangeDatabase("Master")
            Dim sc As SqlClient.SqlCommand = New SqlClient.SqlCommand()
            sc.Connection = mo_conn
            sc.CommandType = CommandType.Text
            sc.CommandText = "SELECT TOP 1 Name FROM SysDatabases WHERE Name='" + ps_database + "'"
            Dim sdr As SqlClient.SqlDataReader = sc.ExecuteReader()
            If (sdr.Read()) Then

                If (Not pb_replace) Then

                    Throw New Exception("Restore failed, Database name already exists")
                End If
            End If
            sdr.Close()
            sc.CommandText = "RESTORE DATABASE " + ps_database + " FROM DISK = '" + ps_filesource _
                                + "' WITH MOVE '" + ps_logical_filename + "' TO '" + ps_physical_filename _
                                + "', MOVE '" + ps_logical_logname + "' TO '" + ps_physical_logname + "'"
            If (pb_replace) Then
                sc.CommandText += ",Replace"
            End If
            sc.ExecuteNonQuery()
            sc.Dispose()
            sc = Nothing
            mo_conn.ChangeDatabase(ls_OldDbName)
        End Sub
             * */
            #endregion



            public void executeTaugeCommand(TaugeCommand[] acmd,params string[] p_output)
            {
                
                using (SqlConnection cn = createConnection())
                {
                    object outvalue = null;
                    string outparam = "";
                    SqlTransaction st = cn.BeginTransaction();
                    int i = 0;
                    foreach (TaugeCommand tc in acmd)
                    {
                        SqlCommand sc = tc.SQL_Command;
                        sc.Connection = cn;
                        sc.Transaction = st;
                        
                        try
                        {
                            switch(tc.Type)
                            {
                                case TaugeCommand.eCOMMANDTYPE.ERROR_ON_EXISTS:
                                case TaugeCommand.eCOMMANDTYPE.ERROR_ON_NOT_EXISTS:
                                    SqlDataReader sdr = sc.ExecuteReader();
                                    if (sdr.Read())
                                    {
                                        if (tc.Type == TaugeCommand.eCOMMANDTYPE.ERROR_ON_EXISTS)
                                        {
                                            sdr.Close();
                                            throw new Exception(tc.Extension.ToString());
                                        }
                                    }
                                    else
                                    {
                                        if (tc.Type == TaugeCommand.eCOMMANDTYPE.ERROR_ON_NOT_EXISTS)
                                        {
                                            sdr.Close();
                                            throw new Exception(tc.Extension.ToString());
                                        }
                                    }
                                    sdr.Close();
                                    sdr = null;
                                    break;                                    
                                case TaugeCommand.eCOMMANDTYPE.EXECUTE_NON_QUERY:
                                case TaugeCommand.eCOMMANDTYPE.EXECUTE_QUERY:
                                    tc.Extension = sc.ExecuteNonQuery();
                                    break;                                
                                case TaugeCommand.eCOMMANDTYPE.RETREIVE_IDENTITY:
                                    tc.Extension = sc.ExecuteScalar();
                                    break;
                                    
                            }                            
                        }
                        catch (Exception ex)
                        {
                            st.Rollback();
                            cn.Close();
                            throw ex;
                        }
                        sc.Connection = null;
                        sc.Transaction = null;
                        i++;
                    }
                    st.Commit();
                    cn.Close();
                }                

            }
        }
    
}
